{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pfk1 ##评分卡函数\n",
    "\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.计算农业普惠金融发展潜力指数(市级)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 以2022年为例\n",
    "data=pd.read_excel(\"2022年全国数据.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['pref_code', 'pref_name', 'GDP（亿元）', '一产总值（亿元）', '一产占比', '农作物播种面积（千公顷）',\n",
       "       '粮食作物面积（千公顷）', '经济作物面积（千公顷）', '国家级龙头企业', '省级龙头企业', '地理产品', '财政收入（亿元）',\n",
       "       '财政支出（亿元）', '财政盈余（亿元）', '中央衔接资金（万元）', '省级衔接资金（万元）', '覆盖广度指数', '支付使用指数',\n",
       "       '保险使用指数', '投资使用指数', '信贷使用指数', '数字化指数', '人口数（万人）', '肉猪出栏头数（万只）',\n",
       "       '农林牧渔业总产值（万元）', '农业总产值（万元）', '林业总产值（万元）', '牧业总产值（万元）', '渔业总产值（万元）',\n",
       "       '人均农业总产值（元/人）', '亩均农业总产值（元/亩）'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=[]\n",
    "\n",
    "for index,row in data.iterrows():\n",
    "    \n",
    "    sjdm = row[\"pref_code\"]\n",
    "    sjmc = row[\"pref_name\"]\n",
    "    \n",
    "    cyjg = pfk1.pdf_cyjg(row)\n",
    "    cyjzl = pfk1.pdf_cyjzl(row)\n",
    "    czzj = pfk1.pdf_czzj(row)\n",
    "    fggd = pfk1.pdf_fggd(row)\n",
    "    sysd = pfk1.pdf_sysd(row)\n",
    "    szhcd = pfk1.pdf_szh(row)\n",
    "    \n",
    "    yczb_df = pfk1.pfk_yczb(row[\"一产占比\"])\n",
    "    nzw_df = pfk1.pfk_nzw(row[\"农作物播种面积（千公顷）\"])\n",
    "    szcl_df = pfk1.pfk_szcl(row[\"肉猪出栏头数（万只）\"])\n",
    "    rjgdp_df = pfk1.pfk_rjgdp(row[\"人均农业总产值（元/人）\"])\n",
    "    mjny_df = pfk1.pfk_mjny(row[\"亩均农业总产值（元/亩）\"])\n",
    "    \n",
    "    db_df = pfk1.pfk_db(row[\"地理产品\"])\n",
    "    gjlt_df = pfk1.pfk_gjlt(row[\"国家级龙头企业\"])\n",
    "    sjlt_df = pfk1.pfk_sjlt(row[\"省级龙头企业\"])\n",
    "    \n",
    "    czsr_df = pfk1.pfk_czsr(row[\"财政收入（亿元）\"])\n",
    "    czzc_df = pfk1.pfk_czzc(row[\"财政支出（亿元）\"])\n",
    "    czyk_df = pfk1.pfk_czyk(row[\"财政盈余（亿元）\"])\n",
    "    zyxj_df = pfk1.pfk_zyxj(row[\"中央衔接资金（万元）\"])\n",
    "    sjxj_df = pfk1.pfk_sjxj(row[\"省级衔接资金（万元）\"])\n",
    "    \n",
    "    fggd_df = pfk1.pfk_fggd(row[\"覆盖广度指数\"])\n",
    "    \n",
    "    zfqj_df = pfk1.pfk_zfqj(row[\"支付使用指数\"])\n",
    "    bxqj_df = pfk1.pfk_bxqj(row[\"保险使用指数\"])\n",
    "    tzqj_df = pfk1.pfk_tzqj(row[\"投资使用指数\"])\n",
    "    xdqj_df = pfk1.pfk_xdqj(row[\"信贷使用指数\"])\n",
    "    \n",
    "    szh_df = pfk1.pfk_szh(row[\"数字化指数\"])\n",
    "    \n",
    "    zs = pfk1.pdf_ZF(row)\n",
    "    \n",
    "    df.append([sjdm,sjmc,cyjg,cyjzl,czzj,fggd,sysd,szhcd,yczb_df , nzw_df , szcl_df , rjgdp_df , mjny_df , db_df , gjlt_df , sjlt_df , czsr_df , czzc_df , czyk_df , zyxj_df , sjxj_df , fggd_df , zfqj_df , bxqj_df , tzqj_df , xdqj_df , szh_df,zs])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.append([sjdm,sjmc,cyjg,cyjzl,czzj,fggd,sysd,szhcd,yczb_df , nzw_df , szcl_df , rjgdp_df , mjny_df , db_df , gjlt_df , sjlt_df , czsr_df , czzc_df , czyk_df , zyxj_df , sjxj_df , fggd_df , zfqj_df , bxqj_df , tzqj_df , xdqj_df , szh_df,zs])\n",
    "df = pd.DataFrame(df,columns=['pref_code','pref_name','产业结构得分','产业竞争力得分','财政资金得分','数字支付覆盖广度得分','金融使用深度得分','数字化程度得分','一产GDP占比','农作物播种面积','农畜出栏量','人均农业总产值','亩均农业总产值','地标产品数量','国家级龙头企业数量','省级龙头企业数量','市级一般公共预算收入','市级一般公共预算支出','市级财政盈余','中央衔接资金','省级衔接资金','覆盖广度指数','支付使用指数','保险使用指数','投资使用指数','信贷使用指数','数字化指数','总得分'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('2022年农业普惠金融发展潜力指数.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pref_code</th>\n",
       "      <th>pref_name</th>\n",
       "      <th>产业结构得分</th>\n",
       "      <th>产业竞争力得分</th>\n",
       "      <th>财政资金得分</th>\n",
       "      <th>数字支付覆盖广度得分</th>\n",
       "      <th>金融使用深度得分</th>\n",
       "      <th>数字化程度得分</th>\n",
       "      <th>一产GDP占比</th>\n",
       "      <th>农作物播种面积</th>\n",
       "      <th>...</th>\n",
       "      <th>市级财政盈余</th>\n",
       "      <th>中央衔接资金</th>\n",
       "      <th>省级衔接资金</th>\n",
       "      <th>覆盖广度指数</th>\n",
       "      <th>支付使用指数</th>\n",
       "      <th>保险使用指数</th>\n",
       "      <th>投资使用指数</th>\n",
       "      <th>信贷使用指数</th>\n",
       "      <th>数字化指数</th>\n",
       "      <th>总得分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1100</td>\n",
       "      <td>北京市</td>\n",
       "      <td>10.14</td>\n",
       "      <td>20.16</td>\n",
       "      <td>8.28</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.04</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.68</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.48</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>67.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1200</td>\n",
       "      <td>天津市</td>\n",
       "      <td>10.92</td>\n",
       "      <td>20.16</td>\n",
       "      <td>9.84</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.04</td>\n",
       "      <td>3.9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.68</td>\n",
       "      <td>2.08</td>\n",
       "      <td>0.48</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>68.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1301</td>\n",
       "      <td>石家庄市</td>\n",
       "      <td>16.12</td>\n",
       "      <td>14.40</td>\n",
       "      <td>13.52</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.6</td>\n",
       "      <td>3.12</td>\n",
       "      <td>5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.68</td>\n",
       "      <td>3.12</td>\n",
       "      <td>3.84</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.6</td>\n",
       "      <td>68.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1302</td>\n",
       "      <td>唐山市</td>\n",
       "      <td>17.94</td>\n",
       "      <td>18.24</td>\n",
       "      <td>9.60</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>3.12</td>\n",
       "      <td>5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>1.36</td>\n",
       "      <td>1.04</td>\n",
       "      <td>1.92</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>62.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1303</td>\n",
       "      <td>秦皇岛市</td>\n",
       "      <td>15.34</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.00</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>9.6</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>2.04</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.88</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>9.6</td>\n",
       "      <td>60.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>6532</td>\n",
       "      <td>和田地区</td>\n",
       "      <td>17.94</td>\n",
       "      <td>16.32</td>\n",
       "      <td>14.60</td>\n",
       "      <td>3.2</td>\n",
       "      <td>2.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>4.16</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>1.36</td>\n",
       "      <td>5.20</td>\n",
       "      <td>4.80</td>\n",
       "      <td>3.2</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>6540</td>\n",
       "      <td>伊犁哈萨克自治州</td>\n",
       "      <td>12.61</td>\n",
       "      <td>19.20</td>\n",
       "      <td>13.84</td>\n",
       "      <td>4.8</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.20</td>\n",
       "      <td>5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.68</td>\n",
       "      <td>4.16</td>\n",
       "      <td>3.84</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>4.8</td>\n",
       "      <td>58.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>6542</td>\n",
       "      <td>塔城地区</td>\n",
       "      <td>18.98</td>\n",
       "      <td>6.72</td>\n",
       "      <td>10.68</td>\n",
       "      <td>6.4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>5.20</td>\n",
       "      <td>3.9</td>\n",
       "      <td>...</td>\n",
       "      <td>2.04</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.88</td>\n",
       "      <td>6.4</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>53.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>6543</td>\n",
       "      <td>阿勒泰地区</td>\n",
       "      <td>14.56</td>\n",
       "      <td>10.56</td>\n",
       "      <td>10.68</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>2.04</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.88</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>51.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>6543</td>\n",
       "      <td>阿勒泰地区</td>\n",
       "      <td>14.56</td>\n",
       "      <td>10.56</td>\n",
       "      <td>10.68</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>2.04</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.88</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>51.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>331 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pref_code pref_name  产业结构得分  产业竞争力得分  财政资金得分  数字支付覆盖广度得分  金融使用深度得分  \\\n",
       "0         1100       北京市   10.14    20.16    8.28         8.0       9.0   \n",
       "1         1200       天津市   10.92    20.16    9.84         8.0       7.5   \n",
       "2         1301      石家庄市   16.12    14.40   13.52         8.0       7.0   \n",
       "3         1302       唐山市   17.94    18.24    9.60         4.8       5.0   \n",
       "4         1303      秦皇岛市   15.34    13.44   12.00         4.8       5.5   \n",
       "..         ...       ...     ...      ...     ...         ...       ...   \n",
       "326       6532      和田地区   17.94    16.32   14.60         3.2       2.5   \n",
       "327       6540  伊犁哈萨克自治州   12.61    19.20   13.84         4.8       3.0   \n",
       "328       6542      塔城地区   18.98     6.72   10.68         6.4       4.0   \n",
       "329       6543     阿勒泰地区   14.56    10.56   10.68         4.8       4.0   \n",
       "330       6543     阿勒泰地区   14.56    10.56   10.68         4.8       4.0   \n",
       "\n",
       "     数字化程度得分  一产GDP占比  农作物播种面积  ...  市级财政盈余  中央衔接资金  省级衔接资金  覆盖广度指数  支付使用指数  \\\n",
       "0       12.0     1.04      2.6  ...    0.68    0.52    0.48     8.0     2.0   \n",
       "1       12.0     1.04      3.9  ...    0.68    2.08    0.48     8.0     1.5   \n",
       "2        9.6     3.12      5.2  ...    0.68    3.12    3.84     8.0     1.5   \n",
       "3        7.2     3.12      5.2  ...    1.36    1.04    1.92     4.8     0.5   \n",
       "4        9.6     3.12      2.6  ...    2.04    3.12    2.88     4.8     1.0   \n",
       "..       ...      ...      ...  ...     ...     ...     ...     ...     ...   \n",
       "326      7.2     4.16      2.6  ...    1.36    5.20    4.80     3.2     0.5   \n",
       "327      4.8     5.20      5.2  ...    0.68    4.16    3.84     4.8     0.5   \n",
       "328      7.2     5.20      3.9  ...    2.04    3.12    2.88     6.4     0.5   \n",
       "329      7.2     3.12      2.6  ...    2.04    3.12    2.88     4.8     0.5   \n",
       "330      7.2     3.12      2.6  ...    2.04    3.12    2.88     4.8     0.5   \n",
       "\n",
       "     保险使用指数  投资使用指数  信贷使用指数  数字化指数    总得分  \n",
       "0       2.5     2.5     2.0   12.0  67.58  \n",
       "1       2.0     2.0     2.0   12.0  68.42  \n",
       "2       1.5     2.0     2.0    9.6  68.64  \n",
       "3       1.5     1.5     1.5    7.2  62.78  \n",
       "4       1.5     1.5     1.5    9.6  60.68  \n",
       "..      ...     ...     ...    ...    ...  \n",
       "326     0.5     0.5     1.0    7.2  61.76  \n",
       "327     0.5     0.5     1.5    4.8  58.25  \n",
       "328     0.5     1.5     1.5    7.2  53.98  \n",
       "329     0.5     1.5     1.5    7.2  51.80  \n",
       "330     0.5     1.5     1.5    7.2  51.80  \n",
       "\n",
       "[331 rows x 28 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.计算农业普惠金融发展潜力指数(省级)\n",
    "#各省指数是基于下辖市级城市指数加权得出，权重依据的是各市一产占比。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pref_code</th>\n",
       "      <th>pref_name</th>\n",
       "      <th>一产占比</th>\n",
       "      <th>prov_code</th>\n",
       "      <th>权重</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1100</td>\n",
       "      <td>北京市</td>\n",
       "      <td>0.002680</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1200</td>\n",
       "      <td>天津市</td>\n",
       "      <td>0.016746</td>\n",
       "      <td>12</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1301</td>\n",
       "      <td>石家庄市</td>\n",
       "      <td>0.078625</td>\n",
       "      <td>13</td>\n",
       "      <td>0.058030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1302</td>\n",
       "      <td>唐山市</td>\n",
       "      <td>0.071725</td>\n",
       "      <td>13</td>\n",
       "      <td>0.052937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1303</td>\n",
       "      <td>秦皇岛市</td>\n",
       "      <td>0.132058</td>\n",
       "      <td>13</td>\n",
       "      <td>0.097467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>6531</td>\n",
       "      <td>喀什地区</td>\n",
       "      <td>0.045382</td>\n",
       "      <td>65</td>\n",
       "      <td>0.022200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>6532</td>\n",
       "      <td>和田地区</td>\n",
       "      <td>0.219019</td>\n",
       "      <td>65</td>\n",
       "      <td>0.107139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>6540</td>\n",
       "      <td>伊犁哈萨克自治州</td>\n",
       "      <td>0.260420</td>\n",
       "      <td>65</td>\n",
       "      <td>0.127392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>6542</td>\n",
       "      <td>塔城地区</td>\n",
       "      <td>0.411114</td>\n",
       "      <td>65</td>\n",
       "      <td>0.201108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>6543</td>\n",
       "      <td>阿勒泰地区</td>\n",
       "      <td>0.153736</td>\n",
       "      <td>65</td>\n",
       "      <td>0.075204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>330 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pref_code pref_name      一产占比 prov_code        权重\n",
       "0         1100       北京市  0.002680        11  1.000000\n",
       "1         1200       天津市  0.016746        12  1.000000\n",
       "2         1301      石家庄市  0.078625        13  0.058030\n",
       "3         1302       唐山市  0.071725        13  0.052937\n",
       "4         1303      秦皇岛市  0.132058        13  0.097467\n",
       "..         ...       ...       ...       ...       ...\n",
       "325       6531      喀什地区  0.045382        65  0.022200\n",
       "326       6532      和田地区  0.219019        65  0.107139\n",
       "327       6540  伊犁哈萨克自治州  0.260420        65  0.127392\n",
       "328       6542      塔城地区  0.411114        65  0.201108\n",
       "329       6543     阿勒泰地区  0.153736        65  0.075204\n",
       "\n",
       "[330 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 由一产占比计算加权权重\n",
    "data=pd.read_excel(\"2022年全国数据.xlsx\")\n",
    "data = data[['pref_code','pref_name','一产占比']]\n",
    "data['prov_code'] = data.pref_code.astype(str).str[:2]\n",
    "data['权重'] =  (data['一产占比'] / data.groupby('prov_code')['一产占比'].transform('sum'))\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pref_code</th>\n",
       "      <th>pref_name</th>\n",
       "      <th>产业结构得分</th>\n",
       "      <th>产业竞争力得分</th>\n",
       "      <th>财政资金得分</th>\n",
       "      <th>数字支付覆盖广度得分</th>\n",
       "      <th>金融使用深度得分</th>\n",
       "      <th>数字化程度得分</th>\n",
       "      <th>一产GDP占比</th>\n",
       "      <th>农作物播种面积</th>\n",
       "      <th>...</th>\n",
       "      <th>省级衔接资金</th>\n",
       "      <th>覆盖广度指数</th>\n",
       "      <th>支付使用指数</th>\n",
       "      <th>保险使用指数</th>\n",
       "      <th>投资使用指数</th>\n",
       "      <th>信贷使用指数</th>\n",
       "      <th>数字化指数</th>\n",
       "      <th>总得分</th>\n",
       "      <th>prov_code</th>\n",
       "      <th>权重</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1100</td>\n",
       "      <td>北京市</td>\n",
       "      <td>10.14</td>\n",
       "      <td>20.16</td>\n",
       "      <td>8.28</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.04</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.48</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>67.58</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1200</td>\n",
       "      <td>天津市</td>\n",
       "      <td>10.92</td>\n",
       "      <td>20.16</td>\n",
       "      <td>9.84</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.04</td>\n",
       "      <td>3.9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.48</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>68.42</td>\n",
       "      <td>12</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1301</td>\n",
       "      <td>石家庄市</td>\n",
       "      <td>16.12</td>\n",
       "      <td>14.40</td>\n",
       "      <td>13.52</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.6</td>\n",
       "      <td>3.12</td>\n",
       "      <td>5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>3.84</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.6</td>\n",
       "      <td>68.64</td>\n",
       "      <td>13</td>\n",
       "      <td>0.058030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1302</td>\n",
       "      <td>唐山市</td>\n",
       "      <td>17.94</td>\n",
       "      <td>18.24</td>\n",
       "      <td>9.60</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>3.12</td>\n",
       "      <td>5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>1.92</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>62.78</td>\n",
       "      <td>13</td>\n",
       "      <td>0.052937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1303</td>\n",
       "      <td>秦皇岛市</td>\n",
       "      <td>15.34</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.00</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>9.6</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>2.88</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>9.6</td>\n",
       "      <td>60.68</td>\n",
       "      <td>13</td>\n",
       "      <td>0.097467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>6532</td>\n",
       "      <td>和田地区</td>\n",
       "      <td>17.94</td>\n",
       "      <td>16.32</td>\n",
       "      <td>14.60</td>\n",
       "      <td>3.2</td>\n",
       "      <td>2.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>4.16</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>4.80</td>\n",
       "      <td>3.2</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>61.76</td>\n",
       "      <td>65</td>\n",
       "      <td>0.107139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>6540</td>\n",
       "      <td>伊犁哈萨克自治州</td>\n",
       "      <td>12.61</td>\n",
       "      <td>19.20</td>\n",
       "      <td>13.84</td>\n",
       "      <td>4.8</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.20</td>\n",
       "      <td>5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>3.84</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>4.8</td>\n",
       "      <td>58.25</td>\n",
       "      <td>65</td>\n",
       "      <td>0.127392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>6542</td>\n",
       "      <td>塔城地区</td>\n",
       "      <td>18.98</td>\n",
       "      <td>6.72</td>\n",
       "      <td>10.68</td>\n",
       "      <td>6.4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>5.20</td>\n",
       "      <td>3.9</td>\n",
       "      <td>...</td>\n",
       "      <td>2.88</td>\n",
       "      <td>6.4</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>53.98</td>\n",
       "      <td>65</td>\n",
       "      <td>0.201108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>6543</td>\n",
       "      <td>阿勒泰地区</td>\n",
       "      <td>14.56</td>\n",
       "      <td>10.56</td>\n",
       "      <td>10.68</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>2.88</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>51.80</td>\n",
       "      <td>65</td>\n",
       "      <td>0.075204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>6543</td>\n",
       "      <td>阿勒泰地区</td>\n",
       "      <td>14.56</td>\n",
       "      <td>10.56</td>\n",
       "      <td>10.68</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.2</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.6</td>\n",
       "      <td>...</td>\n",
       "      <td>2.88</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>51.80</td>\n",
       "      <td>65</td>\n",
       "      <td>0.075204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>331 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pref_code pref_name  产业结构得分  产业竞争力得分  财政资金得分  数字支付覆盖广度得分  金融使用深度得分  \\\n",
       "0         1100       北京市   10.14    20.16    8.28         8.0       9.0   \n",
       "1         1200       天津市   10.92    20.16    9.84         8.0       7.5   \n",
       "2         1301      石家庄市   16.12    14.40   13.52         8.0       7.0   \n",
       "3         1302       唐山市   17.94    18.24    9.60         4.8       5.0   \n",
       "4         1303      秦皇岛市   15.34    13.44   12.00         4.8       5.5   \n",
       "..         ...       ...     ...      ...     ...         ...       ...   \n",
       "326       6532      和田地区   17.94    16.32   14.60         3.2       2.5   \n",
       "327       6540  伊犁哈萨克自治州   12.61    19.20   13.84         4.8       3.0   \n",
       "328       6542      塔城地区   18.98     6.72   10.68         6.4       4.0   \n",
       "329       6543     阿勒泰地区   14.56    10.56   10.68         4.8       4.0   \n",
       "330       6543     阿勒泰地区   14.56    10.56   10.68         4.8       4.0   \n",
       "\n",
       "     数字化程度得分  一产GDP占比  农作物播种面积  ...  省级衔接资金  覆盖广度指数  支付使用指数  保险使用指数  投资使用指数  \\\n",
       "0       12.0     1.04      2.6  ...    0.48     8.0     2.0     2.5     2.5   \n",
       "1       12.0     1.04      3.9  ...    0.48     8.0     1.5     2.0     2.0   \n",
       "2        9.6     3.12      5.2  ...    3.84     8.0     1.5     1.5     2.0   \n",
       "3        7.2     3.12      5.2  ...    1.92     4.8     0.5     1.5     1.5   \n",
       "4        9.6     3.12      2.6  ...    2.88     4.8     1.0     1.5     1.5   \n",
       "..       ...      ...      ...  ...     ...     ...     ...     ...     ...   \n",
       "326      7.2     4.16      2.6  ...    4.80     3.2     0.5     0.5     0.5   \n",
       "327      4.8     5.20      5.2  ...    3.84     4.8     0.5     0.5     0.5   \n",
       "328      7.2     5.20      3.9  ...    2.88     6.4     0.5     0.5     1.5   \n",
       "329      7.2     3.12      2.6  ...    2.88     4.8     0.5     0.5     1.5   \n",
       "330      7.2     3.12      2.6  ...    2.88     4.8     0.5     0.5     1.5   \n",
       "\n",
       "     信贷使用指数  数字化指数    总得分  prov_code        权重  \n",
       "0       2.0   12.0  67.58         11  1.000000  \n",
       "1       2.0   12.0  68.42         12  1.000000  \n",
       "2       2.0    9.6  68.64         13  0.058030  \n",
       "3       1.5    7.2  62.78         13  0.052937  \n",
       "4       1.5    9.6  60.68         13  0.097467  \n",
       "..      ...    ...    ...        ...       ...  \n",
       "326     1.0    7.2  61.76         65  0.107139  \n",
       "327     1.5    4.8  58.25         65  0.127392  \n",
       "328     1.5    7.2  53.98         65  0.201108  \n",
       "329     1.5    7.2  51.80         65  0.075204  \n",
       "330     1.5    7.2  51.80         65  0.075204  \n",
       "\n",
       "[331 rows x 30 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##加权平均计算省级指数\n",
    "data = data[['pref_name','prov_code','权重']]\n",
    "data1 = pd.read_excel(r\".\\2022年农业普惠金融发展潜力指数.xlsx\")\n",
    "data2 = pd.merge(data1,data,on='pref_name')\n",
    "data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prov_code</th>\n",
       "      <th>pref_code</th>\n",
       "      <th>pref_name</th>\n",
       "      <th>权重</th>\n",
       "      <th>产业结构省级得分</th>\n",
       "      <th>产业竞争力省级得分</th>\n",
       "      <th>财政资金省级得分</th>\n",
       "      <th>数字支付覆盖广度省级得分</th>\n",
       "      <th>金融使用深度省级得分</th>\n",
       "      <th>数字化程度省级得分</th>\n",
       "      <th>省级得分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>1100</td>\n",
       "      <td>北京市</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10.140000</td>\n",
       "      <td>20.160000</td>\n",
       "      <td>8.280000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>67.580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>1200</td>\n",
       "      <td>天津市</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10.920000</td>\n",
       "      <td>20.160000</td>\n",
       "      <td>9.840000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>68.420000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>1301</td>\n",
       "      <td>石家庄市</td>\n",
       "      <td>0.058030</td>\n",
       "      <td>0.935439</td>\n",
       "      <td>0.835628</td>\n",
       "      <td>0.784562</td>\n",
       "      <td>0.464238</td>\n",
       "      <td>0.406208</td>\n",
       "      <td>0.557085</td>\n",
       "      <td>3.983160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>1302</td>\n",
       "      <td>唐山市</td>\n",
       "      <td>0.052937</td>\n",
       "      <td>0.949698</td>\n",
       "      <td>0.965580</td>\n",
       "      <td>0.508200</td>\n",
       "      <td>0.254100</td>\n",
       "      <td>0.264687</td>\n",
       "      <td>0.381150</td>\n",
       "      <td>3.323415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>1303</td>\n",
       "      <td>秦皇岛市</td>\n",
       "      <td>0.097467</td>\n",
       "      <td>1.495138</td>\n",
       "      <td>1.309952</td>\n",
       "      <td>1.169600</td>\n",
       "      <td>0.467840</td>\n",
       "      <td>0.536067</td>\n",
       "      <td>0.935680</td>\n",
       "      <td>5.914276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>65</td>\n",
       "      <td>6532</td>\n",
       "      <td>和田地区</td>\n",
       "      <td>0.107139</td>\n",
       "      <td>1.922081</td>\n",
       "      <td>1.748515</td>\n",
       "      <td>1.564235</td>\n",
       "      <td>0.342846</td>\n",
       "      <td>0.267848</td>\n",
       "      <td>0.771404</td>\n",
       "      <td>6.616929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>65</td>\n",
       "      <td>6540</td>\n",
       "      <td>伊犁哈萨克自治州</td>\n",
       "      <td>0.127392</td>\n",
       "      <td>1.606408</td>\n",
       "      <td>2.445918</td>\n",
       "      <td>1.763099</td>\n",
       "      <td>0.611480</td>\n",
       "      <td>0.382175</td>\n",
       "      <td>0.611480</td>\n",
       "      <td>7.420559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>65</td>\n",
       "      <td>6542</td>\n",
       "      <td>塔城地区</td>\n",
       "      <td>0.201108</td>\n",
       "      <td>3.817022</td>\n",
       "      <td>1.351443</td>\n",
       "      <td>2.147829</td>\n",
       "      <td>1.287089</td>\n",
       "      <td>0.804430</td>\n",
       "      <td>1.447975</td>\n",
       "      <td>10.855789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>65</td>\n",
       "      <td>6543</td>\n",
       "      <td>阿勒泰地区</td>\n",
       "      <td>0.075204</td>\n",
       "      <td>1.094976</td>\n",
       "      <td>0.794159</td>\n",
       "      <td>0.803183</td>\n",
       "      <td>0.360981</td>\n",
       "      <td>0.300818</td>\n",
       "      <td>0.541472</td>\n",
       "      <td>3.895589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>65</td>\n",
       "      <td>6543</td>\n",
       "      <td>阿勒泰地区</td>\n",
       "      <td>0.075204</td>\n",
       "      <td>1.094976</td>\n",
       "      <td>0.794159</td>\n",
       "      <td>0.803183</td>\n",
       "      <td>0.360981</td>\n",
       "      <td>0.300818</td>\n",
       "      <td>0.541472</td>\n",
       "      <td>3.895589</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>331 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    prov_code  pref_code pref_name        权重   产业结构省级得分  产业竞争力省级得分  财政资金省级得分  \\\n",
       "0          11       1100       北京市  1.000000  10.140000  20.160000  8.280000   \n",
       "1          12       1200       天津市  1.000000  10.920000  20.160000  9.840000   \n",
       "2          13       1301      石家庄市  0.058030   0.935439   0.835628  0.784562   \n",
       "3          13       1302       唐山市  0.052937   0.949698   0.965580  0.508200   \n",
       "4          13       1303      秦皇岛市  0.097467   1.495138   1.309952  1.169600   \n",
       "..        ...        ...       ...       ...        ...        ...       ...   \n",
       "326        65       6532      和田地区  0.107139   1.922081   1.748515  1.564235   \n",
       "327        65       6540  伊犁哈萨克自治州  0.127392   1.606408   2.445918  1.763099   \n",
       "328        65       6542      塔城地区  0.201108   3.817022   1.351443  2.147829   \n",
       "329        65       6543     阿勒泰地区  0.075204   1.094976   0.794159  0.803183   \n",
       "330        65       6543     阿勒泰地区  0.075204   1.094976   0.794159  0.803183   \n",
       "\n",
       "     数字支付覆盖广度省级得分  金融使用深度省级得分  数字化程度省级得分       省级得分  \n",
       "0        8.000000    9.000000  12.000000  67.580000  \n",
       "1        8.000000    7.500000  12.000000  68.420000  \n",
       "2        0.464238    0.406208   0.557085   3.983160  \n",
       "3        0.254100    0.264687   0.381150   3.323415  \n",
       "4        0.467840    0.536067   0.935680   5.914276  \n",
       "..            ...         ...        ...        ...  \n",
       "326      0.342846    0.267848   0.771404   6.616929  \n",
       "327      0.611480    0.382175   0.611480   7.420559  \n",
       "328      1.287089    0.804430   1.447975  10.855789  \n",
       "329      0.360981    0.300818   0.541472   3.895589  \n",
       "330      0.360981    0.300818   0.541472   3.895589  \n",
       "\n",
       "[331 rows x 11 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2['产业结构省级得分'] = data2['产业结构得分'] * data2['权重']\n",
    "data2['产业竞争力省级得分'] = data2['产业竞争力得分'] * data2['权重']\n",
    "data2['财政资金省级得分'] = data2['财政资金得分'] * data2['权重']\n",
    "data2['数字支付覆盖广度省级得分'] = data2['数字支付覆盖广度得分'] * data2['权重']\n",
    "data2['金融使用深度省级得分'] = data2['金融使用深度得分'] * data2['权重']\n",
    "data2['数字化程度省级得分'] = data2['数字化程度得分'] * data2['权重']\n",
    "data2['省级得分'] = data2['总得分'] * data2['权重']\n",
    "df = data2[['prov_code','pref_code','pref_name','权重','产业结构省级得分','产业竞争力省级得分','财政资金省级得分','数字支付覆盖广度省级得分','金融使用深度省级得分','数字化程度省级得分','省级得分']]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>产业结构省级得分</th>\n",
       "      <th>产业竞争力省级得分</th>\n",
       "      <th>财政资金省级得分</th>\n",
       "      <th>数字支付覆盖广度省级得分</th>\n",
       "      <th>金融使用深度省级得分</th>\n",
       "      <th>数字化程度省级得分</th>\n",
       "      <th>省级得分</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>prov_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10.140000</td>\n",
       "      <td>20.160000</td>\n",
       "      <td>8.280000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>67.580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10.920000</td>\n",
       "      <td>20.160000</td>\n",
       "      <td>9.840000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>68.420000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>17.082121</td>\n",
       "      <td>15.310590</td>\n",
       "      <td>13.774557</td>\n",
       "      <td>5.181445</td>\n",
       "      <td>5.274470</td>\n",
       "      <td>8.891908</td>\n",
       "      <td>65.515092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14.725410</td>\n",
       "      <td>16.500948</td>\n",
       "      <td>13.190888</td>\n",
       "      <td>7.066611</td>\n",
       "      <td>4.377528</td>\n",
       "      <td>7.232418</td>\n",
       "      <td>63.093804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>18.280505</td>\n",
       "      <td>15.681274</td>\n",
       "      <td>12.734841</td>\n",
       "      <td>5.480377</td>\n",
       "      <td>3.867692</td>\n",
       "      <td>5.176191</td>\n",
       "      <td>61.220881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>15.543864</td>\n",
       "      <td>11.926403</td>\n",
       "      <td>10.402007</td>\n",
       "      <td>4.847688</td>\n",
       "      <td>4.859993</td>\n",
       "      <td>6.514506</td>\n",
       "      <td>54.094461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>16.482689</td>\n",
       "      <td>10.248507</td>\n",
       "      <td>8.714594</td>\n",
       "      <td>4.887537</td>\n",
       "      <td>4.046863</td>\n",
       "      <td>5.302158</td>\n",
       "      <td>49.682349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>19.925871</td>\n",
       "      <td>13.385941</td>\n",
       "      <td>11.401387</td>\n",
       "      <td>4.742190</td>\n",
       "      <td>4.685449</td>\n",
       "      <td>6.456815</td>\n",
       "      <td>60.597654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>10.920000</td>\n",
       "      <td>22.080000</td>\n",
       "      <td>8.280000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>71.280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>16.999078</td>\n",
       "      <td>16.398692</td>\n",
       "      <td>11.111328</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>7.593786</td>\n",
       "      <td>11.337320</td>\n",
       "      <td>71.440204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>11.548006</td>\n",
       "      <td>15.642958</td>\n",
       "      <td>10.682368</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.960434</td>\n",
       "      <td>11.525668</td>\n",
       "      <td>66.359434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>15.089620</td>\n",
       "      <td>13.568508</td>\n",
       "      <td>13.005738</td>\n",
       "      <td>6.357982</td>\n",
       "      <td>6.824441</td>\n",
       "      <td>10.695486</td>\n",
       "      <td>65.541775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>16.850745</td>\n",
       "      <td>18.327539</td>\n",
       "      <td>9.405378</td>\n",
       "      <td>7.627762</td>\n",
       "      <td>7.953268</td>\n",
       "      <td>8.178637</td>\n",
       "      <td>68.343329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>14.699198</td>\n",
       "      <td>15.315162</td>\n",
       "      <td>13.637754</td>\n",
       "      <td>5.943911</td>\n",
       "      <td>6.515072</td>\n",
       "      <td>9.167962</td>\n",
       "      <td>65.279060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>16.543308</td>\n",
       "      <td>17.463131</td>\n",
       "      <td>11.970059</td>\n",
       "      <td>7.006522</td>\n",
       "      <td>6.260068</td>\n",
       "      <td>11.108052</td>\n",
       "      <td>70.351141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>18.052779</td>\n",
       "      <td>14.975414</td>\n",
       "      <td>12.361961</td>\n",
       "      <td>6.271108</td>\n",
       "      <td>5.899075</td>\n",
       "      <td>9.273982</td>\n",
       "      <td>66.834319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>17.097377</td>\n",
       "      <td>15.345059</td>\n",
       "      <td>11.456096</td>\n",
       "      <td>6.187440</td>\n",
       "      <td>6.631720</td>\n",
       "      <td>8.110243</td>\n",
       "      <td>64.827935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>16.777094</td>\n",
       "      <td>14.261022</td>\n",
       "      <td>12.204656</td>\n",
       "      <td>4.997119</td>\n",
       "      <td>5.652992</td>\n",
       "      <td>7.781593</td>\n",
       "      <td>61.674475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>16.202862</td>\n",
       "      <td>10.420055</td>\n",
       "      <td>9.943318</td>\n",
       "      <td>5.920172</td>\n",
       "      <td>7.064906</td>\n",
       "      <td>7.994363</td>\n",
       "      <td>57.545677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>17.378131</td>\n",
       "      <td>11.533481</td>\n",
       "      <td>13.363790</td>\n",
       "      <td>5.359728</td>\n",
       "      <td>5.353679</td>\n",
       "      <td>7.313962</td>\n",
       "      <td>60.302771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>15.059741</td>\n",
       "      <td>8.322812</td>\n",
       "      <td>11.445326</td>\n",
       "      <td>7.202098</td>\n",
       "      <td>6.435401</td>\n",
       "      <td>6.003147</td>\n",
       "      <td>54.468525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>19.240000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>16.320000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>7.200000</td>\n",
       "      <td>81.260000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>17.078273</td>\n",
       "      <td>12.072731</td>\n",
       "      <td>12.855441</td>\n",
       "      <td>4.730229</td>\n",
       "      <td>5.032435</td>\n",
       "      <td>7.181954</td>\n",
       "      <td>58.951063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>18.746956</td>\n",
       "      <td>16.276008</td>\n",
       "      <td>13.293742</td>\n",
       "      <td>5.520613</td>\n",
       "      <td>3.485751</td>\n",
       "      <td>5.401405</td>\n",
       "      <td>62.724475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>17.303146</td>\n",
       "      <td>9.127259</td>\n",
       "      <td>12.702500</td>\n",
       "      <td>4.907246</td>\n",
       "      <td>4.423374</td>\n",
       "      <td>6.327163</td>\n",
       "      <td>54.790689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>17.243923</td>\n",
       "      <td>14.979415</td>\n",
       "      <td>13.247229</td>\n",
       "      <td>5.431214</td>\n",
       "      <td>5.362139</td>\n",
       "      <td>8.205255</td>\n",
       "      <td>64.469176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>16.649695</td>\n",
       "      <td>13.612837</td>\n",
       "      <td>15.670558</td>\n",
       "      <td>5.486673</td>\n",
       "      <td>3.523733</td>\n",
       "      <td>5.787334</td>\n",
       "      <td>60.730830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>10.968176</td>\n",
       "      <td>8.451876</td>\n",
       "      <td>11.820202</td>\n",
       "      <td>3.433079</td>\n",
       "      <td>2.670562</td>\n",
       "      <td>5.332129</td>\n",
       "      <td>42.676024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>15.031127</td>\n",
       "      <td>15.706501</td>\n",
       "      <td>10.577890</td>\n",
       "      <td>5.176964</td>\n",
       "      <td>3.235602</td>\n",
       "      <td>5.209700</td>\n",
       "      <td>54.937783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>15.892241</td>\n",
       "      <td>12.557067</td>\n",
       "      <td>12.997410</td>\n",
       "      <td>5.882980</td>\n",
       "      <td>3.748521</td>\n",
       "      <td>7.066606</td>\n",
       "      <td>58.144825</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            产业结构省级得分  产业竞争力省级得分   财政资金省级得分  数字支付覆盖广度省级得分  金融使用深度省级得分  \\\n",
       "prov_code                                                              \n",
       "11         10.140000  20.160000   8.280000      8.000000    9.000000   \n",
       "12         10.920000  20.160000   9.840000      8.000000    7.500000   \n",
       "13         17.082121  15.310590  13.774557      5.181445    5.274470   \n",
       "14         14.725410  16.500948  13.190888      7.066611    4.377528   \n",
       "15         18.280505  15.681274  12.734841      5.480377    3.867692   \n",
       "21         15.543864  11.926403  10.402007      4.847688    4.859993   \n",
       "22         16.482689  10.248507   8.714594      4.887537    4.046863   \n",
       "23         19.925871  13.385941  11.401387      4.742190    4.685449   \n",
       "31         10.920000  22.080000   8.280000      8.000000   10.000000   \n",
       "32         16.999078  16.398692  11.111328      8.000000    7.593786   \n",
       "33         11.548006  15.642958  10.682368      8.000000    8.960434   \n",
       "34         15.089620  13.568508  13.005738      6.357982    6.824441   \n",
       "35         16.850745  18.327539   9.405378      7.627762    7.953268   \n",
       "36         14.699198  15.315162  13.637754      5.943911    6.515072   \n",
       "37         16.543308  17.463131  11.970059      7.006522    6.260068   \n",
       "41         18.052779  14.975414  12.361961      6.271108    5.899075   \n",
       "42         17.097377  15.345059  11.456096      6.187440    6.631720   \n",
       "43         16.777094  14.261022  12.204656      4.997119    5.652992   \n",
       "44         16.202862  10.420055   9.943318      5.920172    7.064906   \n",
       "45         17.378131  11.533481  13.363790      5.359728    5.353679   \n",
       "46         15.059741   8.322812  11.445326      7.202098    6.435401   \n",
       "50         19.240000  24.000000  16.320000      8.000000    6.500000   \n",
       "51         17.078273  12.072731  12.855441      4.730229    5.032435   \n",
       "52         18.746956  16.276008  13.293742      5.520613    3.485751   \n",
       "53         17.303146   9.127259  12.702500      4.907246    4.423374   \n",
       "61         17.243923  14.979415  13.247229      5.431214    5.362139   \n",
       "62         16.649695  13.612837  15.670558      5.486673    3.523733   \n",
       "63         10.968176   8.451876  11.820202      3.433079    2.670562   \n",
       "64         15.031127  15.706501  10.577890      5.176964    3.235602   \n",
       "65         15.892241  12.557067  12.997410      5.882980    3.748521   \n",
       "\n",
       "           数字化程度省级得分       省级得分  \n",
       "prov_code                        \n",
       "11         12.000000  67.580000  \n",
       "12         12.000000  68.420000  \n",
       "13          8.891908  65.515092  \n",
       "14          7.232418  63.093804  \n",
       "15          5.176191  61.220881  \n",
       "21          6.514506  54.094461  \n",
       "22          5.302158  49.682349  \n",
       "23          6.456815  60.597654  \n",
       "31         12.000000  71.280000  \n",
       "32         11.337320  71.440204  \n",
       "33         11.525668  66.359434  \n",
       "34         10.695486  65.541775  \n",
       "35          8.178637  68.343329  \n",
       "36          9.167962  65.279060  \n",
       "37         11.108052  70.351141  \n",
       "41          9.273982  66.834319  \n",
       "42          8.110243  64.827935  \n",
       "43          7.781593  61.674475  \n",
       "44          7.994363  57.545677  \n",
       "45          7.313962  60.302771  \n",
       "46          6.003147  54.468525  \n",
       "50          7.200000  81.260000  \n",
       "51          7.181954  58.951063  \n",
       "52          5.401405  62.724475  \n",
       "53          6.327163  54.790689  \n",
       "61          8.205255  64.469176  \n",
       "62          5.787334  60.730830  \n",
       "63          5.332129  42.676024  \n",
       "64          5.209700  54.937783  \n",
       "65          7.066606  58.144825  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d=pd.DataFrame()\n",
    "d['产业结构省级得分']=df.groupby('prov_code')['产业结构省级得分'].agg(np.sum)\n",
    "d['产业竞争力省级得分']=df.groupby('prov_code')['产业竞争力省级得分'].agg(np.sum)\n",
    "d['财政资金省级得分']=df.groupby('prov_code')['财政资金省级得分'].agg(np.sum)\n",
    "d['数字支付覆盖广度省级得分']=df.groupby('prov_code')['数字支付覆盖广度省级得分'].agg(np.sum)\n",
    "d['金融使用深度省级得分']=df.groupby('prov_code')['金融使用深度省级得分'].agg(np.sum)\n",
    "d['数字化程度省级得分']=df.groupby('prov_code')['数字化程度省级得分'].agg(np.sum)\n",
    "d['省级得分']=df.groupby('prov_code')['省级得分'].agg(np.sum)\n",
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dm = pd.read_excel(r\".\\城市代码.xlsx\")\n",
    "dm[\"prov_code\"] = dm[\"prov_code\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prov_code</th>\n",
       "      <th>产业结构省级得分</th>\n",
       "      <th>产业竞争力省级得分</th>\n",
       "      <th>财政资金省级得分</th>\n",
       "      <th>数字支付覆盖广度省级得分</th>\n",
       "      <th>金融使用深度省级得分</th>\n",
       "      <th>数字化程度省级得分</th>\n",
       "      <th>省级得分</th>\n",
       "      <th>prov_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>10.140000</td>\n",
       "      <td>20.160000</td>\n",
       "      <td>8.280000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>67.580000</td>\n",
       "      <td>北京市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>10.920000</td>\n",
       "      <td>20.160000</td>\n",
       "      <td>9.840000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>68.420000</td>\n",
       "      <td>天津市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>17.082121</td>\n",
       "      <td>15.310590</td>\n",
       "      <td>13.774557</td>\n",
       "      <td>5.181445</td>\n",
       "      <td>5.274470</td>\n",
       "      <td>8.891908</td>\n",
       "      <td>65.515092</td>\n",
       "      <td>河北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>14.725410</td>\n",
       "      <td>16.500948</td>\n",
       "      <td>13.190888</td>\n",
       "      <td>7.066611</td>\n",
       "      <td>4.377528</td>\n",
       "      <td>7.232418</td>\n",
       "      <td>63.093804</td>\n",
       "      <td>山西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "      <td>18.280505</td>\n",
       "      <td>15.681274</td>\n",
       "      <td>12.734841</td>\n",
       "      <td>5.480377</td>\n",
       "      <td>3.867692</td>\n",
       "      <td>5.176191</td>\n",
       "      <td>61.220881</td>\n",
       "      <td>内蒙古自治区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>21</td>\n",
       "      <td>15.543864</td>\n",
       "      <td>11.926403</td>\n",
       "      <td>10.402007</td>\n",
       "      <td>4.847688</td>\n",
       "      <td>4.859993</td>\n",
       "      <td>6.514506</td>\n",
       "      <td>54.094461</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>22</td>\n",
       "      <td>16.482689</td>\n",
       "      <td>10.248507</td>\n",
       "      <td>8.714594</td>\n",
       "      <td>4.887537</td>\n",
       "      <td>4.046863</td>\n",
       "      <td>5.302158</td>\n",
       "      <td>49.682349</td>\n",
       "      <td>吉林省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>23</td>\n",
       "      <td>19.925871</td>\n",
       "      <td>13.385941</td>\n",
       "      <td>11.401387</td>\n",
       "      <td>4.742190</td>\n",
       "      <td>4.685449</td>\n",
       "      <td>6.456815</td>\n",
       "      <td>60.597654</td>\n",
       "      <td>黑龙江省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>31</td>\n",
       "      <td>10.920000</td>\n",
       "      <td>22.080000</td>\n",
       "      <td>8.280000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>71.280000</td>\n",
       "      <td>上海市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>32</td>\n",
       "      <td>16.999078</td>\n",
       "      <td>16.398692</td>\n",
       "      <td>11.111328</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>7.593786</td>\n",
       "      <td>11.337320</td>\n",
       "      <td>71.440204</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>33</td>\n",
       "      <td>11.548006</td>\n",
       "      <td>15.642958</td>\n",
       "      <td>10.682368</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.960434</td>\n",
       "      <td>11.525668</td>\n",
       "      <td>66.359434</td>\n",
       "      <td>浙江省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>34</td>\n",
       "      <td>15.089620</td>\n",
       "      <td>13.568508</td>\n",
       "      <td>13.005738</td>\n",
       "      <td>6.357982</td>\n",
       "      <td>6.824441</td>\n",
       "      <td>10.695486</td>\n",
       "      <td>65.541775</td>\n",
       "      <td>安徽省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>35</td>\n",
       "      <td>16.850745</td>\n",
       "      <td>18.327539</td>\n",
       "      <td>9.405378</td>\n",
       "      <td>7.627762</td>\n",
       "      <td>7.953268</td>\n",
       "      <td>8.178637</td>\n",
       "      <td>68.343329</td>\n",
       "      <td>福建省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>36</td>\n",
       "      <td>14.699198</td>\n",
       "      <td>15.315162</td>\n",
       "      <td>13.637754</td>\n",
       "      <td>5.943911</td>\n",
       "      <td>6.515072</td>\n",
       "      <td>9.167962</td>\n",
       "      <td>65.279060</td>\n",
       "      <td>江西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>37</td>\n",
       "      <td>16.543308</td>\n",
       "      <td>17.463131</td>\n",
       "      <td>11.970059</td>\n",
       "      <td>7.006522</td>\n",
       "      <td>6.260068</td>\n",
       "      <td>11.108052</td>\n",
       "      <td>70.351141</td>\n",
       "      <td>山东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>41</td>\n",
       "      <td>18.052779</td>\n",
       "      <td>14.975414</td>\n",
       "      <td>12.361961</td>\n",
       "      <td>6.271108</td>\n",
       "      <td>5.899075</td>\n",
       "      <td>9.273982</td>\n",
       "      <td>66.834319</td>\n",
       "      <td>河南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>42</td>\n",
       "      <td>17.097377</td>\n",
       "      <td>15.345059</td>\n",
       "      <td>11.456096</td>\n",
       "      <td>6.187440</td>\n",
       "      <td>6.631720</td>\n",
       "      <td>8.110243</td>\n",
       "      <td>64.827935</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>43</td>\n",
       "      <td>16.777094</td>\n",
       "      <td>14.261022</td>\n",
       "      <td>12.204656</td>\n",
       "      <td>4.997119</td>\n",
       "      <td>5.652992</td>\n",
       "      <td>7.781593</td>\n",
       "      <td>61.674475</td>\n",
       "      <td>湖南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>44</td>\n",
       "      <td>16.202862</td>\n",
       "      <td>10.420055</td>\n",
       "      <td>9.943318</td>\n",
       "      <td>5.920172</td>\n",
       "      <td>7.064906</td>\n",
       "      <td>7.994363</td>\n",
       "      <td>57.545677</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>45</td>\n",
       "      <td>17.378131</td>\n",
       "      <td>11.533481</td>\n",
       "      <td>13.363790</td>\n",
       "      <td>5.359728</td>\n",
       "      <td>5.353679</td>\n",
       "      <td>7.313962</td>\n",
       "      <td>60.302771</td>\n",
       "      <td>广西壮族自治区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>46</td>\n",
       "      <td>15.059741</td>\n",
       "      <td>8.322812</td>\n",
       "      <td>11.445326</td>\n",
       "      <td>7.202098</td>\n",
       "      <td>6.435401</td>\n",
       "      <td>6.003147</td>\n",
       "      <td>54.468525</td>\n",
       "      <td>海南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>50</td>\n",
       "      <td>19.240000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>16.320000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>7.200000</td>\n",
       "      <td>81.260000</td>\n",
       "      <td>重庆市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>51</td>\n",
       "      <td>17.078273</td>\n",
       "      <td>12.072731</td>\n",
       "      <td>12.855441</td>\n",
       "      <td>4.730229</td>\n",
       "      <td>5.032435</td>\n",
       "      <td>7.181954</td>\n",
       "      <td>58.951063</td>\n",
       "      <td>四川省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>52</td>\n",
       "      <td>18.746956</td>\n",
       "      <td>16.276008</td>\n",
       "      <td>13.293742</td>\n",
       "      <td>5.520613</td>\n",
       "      <td>3.485751</td>\n",
       "      <td>5.401405</td>\n",
       "      <td>62.724475</td>\n",
       "      <td>贵州省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>53</td>\n",
       "      <td>17.303146</td>\n",
       "      <td>9.127259</td>\n",
       "      <td>12.702500</td>\n",
       "      <td>4.907246</td>\n",
       "      <td>4.423374</td>\n",
       "      <td>6.327163</td>\n",
       "      <td>54.790689</td>\n",
       "      <td>云南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>61</td>\n",
       "      <td>17.243923</td>\n",
       "      <td>14.979415</td>\n",
       "      <td>13.247229</td>\n",
       "      <td>5.431214</td>\n",
       "      <td>5.362139</td>\n",
       "      <td>8.205255</td>\n",
       "      <td>64.469176</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>62</td>\n",
       "      <td>16.649695</td>\n",
       "      <td>13.612837</td>\n",
       "      <td>15.670558</td>\n",
       "      <td>5.486673</td>\n",
       "      <td>3.523733</td>\n",
       "      <td>5.787334</td>\n",
       "      <td>60.730830</td>\n",
       "      <td>甘肃省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>63</td>\n",
       "      <td>10.968176</td>\n",
       "      <td>8.451876</td>\n",
       "      <td>11.820202</td>\n",
       "      <td>3.433079</td>\n",
       "      <td>2.670562</td>\n",
       "      <td>5.332129</td>\n",
       "      <td>42.676024</td>\n",
       "      <td>青海省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>64</td>\n",
       "      <td>15.031127</td>\n",
       "      <td>15.706501</td>\n",
       "      <td>10.577890</td>\n",
       "      <td>5.176964</td>\n",
       "      <td>3.235602</td>\n",
       "      <td>5.209700</td>\n",
       "      <td>54.937783</td>\n",
       "      <td>宁夏回族自治区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>65</td>\n",
       "      <td>15.892241</td>\n",
       "      <td>12.557067</td>\n",
       "      <td>12.997410</td>\n",
       "      <td>5.882980</td>\n",
       "      <td>3.748521</td>\n",
       "      <td>7.066606</td>\n",
       "      <td>58.144825</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   prov_code   产业结构省级得分  产业竞争力省级得分   财政资金省级得分  数字支付覆盖广度省级得分  金融使用深度省级得分  \\\n",
       "0         11  10.140000  20.160000   8.280000      8.000000    9.000000   \n",
       "1         12  10.920000  20.160000   9.840000      8.000000    7.500000   \n",
       "2         13  17.082121  15.310590  13.774557      5.181445    5.274470   \n",
       "3         14  14.725410  16.500948  13.190888      7.066611    4.377528   \n",
       "4         15  18.280505  15.681274  12.734841      5.480377    3.867692   \n",
       "5         21  15.543864  11.926403  10.402007      4.847688    4.859993   \n",
       "6         22  16.482689  10.248507   8.714594      4.887537    4.046863   \n",
       "7         23  19.925871  13.385941  11.401387      4.742190    4.685449   \n",
       "8         31  10.920000  22.080000   8.280000      8.000000   10.000000   \n",
       "9         32  16.999078  16.398692  11.111328      8.000000    7.593786   \n",
       "10        33  11.548006  15.642958  10.682368      8.000000    8.960434   \n",
       "11        34  15.089620  13.568508  13.005738      6.357982    6.824441   \n",
       "12        35  16.850745  18.327539   9.405378      7.627762    7.953268   \n",
       "13        36  14.699198  15.315162  13.637754      5.943911    6.515072   \n",
       "14        37  16.543308  17.463131  11.970059      7.006522    6.260068   \n",
       "15        41  18.052779  14.975414  12.361961      6.271108    5.899075   \n",
       "16        42  17.097377  15.345059  11.456096      6.187440    6.631720   \n",
       "17        43  16.777094  14.261022  12.204656      4.997119    5.652992   \n",
       "18        44  16.202862  10.420055   9.943318      5.920172    7.064906   \n",
       "19        45  17.378131  11.533481  13.363790      5.359728    5.353679   \n",
       "20        46  15.059741   8.322812  11.445326      7.202098    6.435401   \n",
       "21        50  19.240000  24.000000  16.320000      8.000000    6.500000   \n",
       "22        51  17.078273  12.072731  12.855441      4.730229    5.032435   \n",
       "23        52  18.746956  16.276008  13.293742      5.520613    3.485751   \n",
       "24        53  17.303146   9.127259  12.702500      4.907246    4.423374   \n",
       "25        61  17.243923  14.979415  13.247229      5.431214    5.362139   \n",
       "26        62  16.649695  13.612837  15.670558      5.486673    3.523733   \n",
       "27        63  10.968176   8.451876  11.820202      3.433079    2.670562   \n",
       "28        64  15.031127  15.706501  10.577890      5.176964    3.235602   \n",
       "29        65  15.892241  12.557067  12.997410      5.882980    3.748521   \n",
       "\n",
       "    数字化程度省级得分       省级得分 prov_name  \n",
       "0   12.000000  67.580000       北京市  \n",
       "1   12.000000  68.420000       天津市  \n",
       "2    8.891908  65.515092       河北省  \n",
       "3    7.232418  63.093804       山西省  \n",
       "4    5.176191  61.220881    内蒙古自治区  \n",
       "5    6.514506  54.094461       辽宁省  \n",
       "6    5.302158  49.682349       吉林省  \n",
       "7    6.456815  60.597654      黑龙江省  \n",
       "8   12.000000  71.280000       上海市  \n",
       "9   11.337320  71.440204       江苏省  \n",
       "10  11.525668  66.359434       浙江省  \n",
       "11  10.695486  65.541775       安徽省  \n",
       "12   8.178637  68.343329       福建省  \n",
       "13   9.167962  65.279060       江西省  \n",
       "14  11.108052  70.351141       山东省  \n",
       "15   9.273982  66.834319       河南省  \n",
       "16   8.110243  64.827935       湖北省  \n",
       "17   7.781593  61.674475       湖南省  \n",
       "18   7.994363  57.545677       广东省  \n",
       "19   7.313962  60.302771   广西壮族自治区  \n",
       "20   6.003147  54.468525       海南省  \n",
       "21   7.200000  81.260000       重庆市  \n",
       "22   7.181954  58.951063       四川省  \n",
       "23   5.401405  62.724475       贵州省  \n",
       "24   6.327163  54.790689       云南省  \n",
       "25   8.205255  64.469176       陕西省  \n",
       "26   5.787334  60.730830       甘肃省  \n",
       "27   5.332129  42.676024       青海省  \n",
       "28   5.209700  54.937783   宁夏回族自治区  \n",
       "29   7.066606  58.144825  新疆维吾尔自治区  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_prov = pd.merge(d,dm, on='prov_code')\n",
    "data_prov.to_excel('2022年农业普惠金融发展潜力指数(省级).xlsx',index = False)\n",
    "data_prov"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.画图展示中国区域农业普惠金融发展潜力指数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_prov[\"prov_name\"].replace(\n",
    "    regex=True,\n",
    "    inplace=True,\n",
    "    #to_replace=[\"省\", \"市\", \"维吾尔自治区\", \"回族自治区\", \"壮族自治区\", \"自治区\"],\n",
    "    #value=r\"\",\n",
    ")\n",
    "\n",
    "province = list(data_prov[\"prov_name\"])\n",
    "value = list(data_prov[\"省级得分\"].round(2))\n",
    "s = min(value)\n",
    "t = max(value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 显示全国地图\n",
    "def map_china() -> Map:\n",
    "    c = (\n",
    "        Map(init_opts=opts.InitOpts(height=\"1000px\", width=\"1500px\"))\n",
    "        .add(\"\",[list(z) for z in zip(province,value)], maptype=\"china\")\n",
    "        .set_global_opts(\n",
    "            title_opts=opts.TitleOpts(title=\"2022年农业普惠金融发展潜力指数\",\n",
    "                                      title_textstyle_opts=opts.TextStyleOpts(font_size=40)),\n",
    "            visualmap_opts=opts.VisualMapOpts(min_=s, max_=t, range_color=[\"#f0f0f0\", \"#808080\"],\n",
    "            textstyle_opts=opts.TextStyleOpts(font_size=18),\n",
    "            orient=\"vertical\"),  # 垂直显示\n",
    "        ).set_series_opts(label_opts=opts.LabelOpts(font_size=18))\n",
    "    )\n",
    "    return c\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    gd = map_china()\n",
    "    gd.render(path=\"2022年农业普惠金融发展潜力指数.html\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "toc-autonumbering": true,
  "toc-showcode": true,
  "toc-showmarkdowntxt": true
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
